package com.hectopascal.goalance.repository;

import com.hectopascal.goalance.pojo.Department;
import com.hectopascal.goalance.pojo.dto.UserBack;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Optional;

@Repository
public interface DepartmentRepository extends CrudRepository<Department, Integer> {
    //根据名字和Id查找部门，查找不是本id的department
    @Query(value = "SELECT * FROM department WHERE name = :name AND id != :id", nativeQuery = true)
    Optional<Department> findByNameAndId(String name, Integer id);

    //根据名字查找部门
    @Query(value = "SELECT * FROM department WHERE name = :name", nativeQuery = true)
    Optional<Department> findByName(String name);

    //模糊查询的总数
    @Query(value = "SELECT COUNT(*) FROM department WHERE " +
            "(name LIKE CONCAT('%', :name, '%') OR :name IS NULL) AND " +
            "(status = :status OR :status IS NULL) " , nativeQuery = true)
    Optional<Integer> findAllDepartmentsPage(String name, Integer status);

    //模糊查询，分页查询
    @Query(value = "SELECT * FROM department WHERE " +
            "(name LIKE CONCAT('%', :name, '%') OR :name IS NULL) AND " +
            "(status = :status OR :status IS NULL) " +
            "LIMIT :size OFFSET :offset", nativeQuery = true)
    Optional<List<Department>> findAllDepartments(@Param("offset") Integer offset,
                                                  @Param("size") Integer size,
                                                  @Param("name") String name,
                                                  @Param("status") Integer status);

    //动态SQL更新部门,parentId可以为null
    @Modifying
    @Transactional
    @Query(value = "UPDATE department SET " +
            "name = CASE WHEN :name IS NOT NULL THEN :name ELSE name END, " +
            "description = CASE WHEN :description IS NOT NULL THEN :description ELSE description END, " +
            "parent_id = :parentId ," +
            "status = CASE WHEN :status IS NOT NULL THEN :status ELSE status END " +
            "WHERE id = :id",
            nativeQuery =true)
    int updateDepartment(String name, String description, Integer parentId, Integer status, Integer id);

    //查找子部门
    @Query(value = "SELECT * FROM department WHERE parent_id = :parentId", nativeQuery = true)
    Optional<List<Department>> findByParentId(Integer parentId);

    //根据id查询user中成员
    @Query(value = "SELECT u.*, dep.name AS departmentname " +
            "FROM user u " +
            "LEFT JOIN department dep ON u.department_id = dep.id " +
            "WHERE department_id = :departmentId", nativeQuery = true)
    Optional<List<UserBack>> findByDepartmentId(Integer departmentId);

    //根据id删除部门
    @Modifying
    @Transactional
    @Query(value = "DELETE FROM department WHERE id = :id", nativeQuery = true)
    int deleteDepartmentById(Integer id);
}
